package com.itheima.mapper;

import com.itheima.pojo.HotSetmeal;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ReportDataMapper {
    //今日新增会员
    @Select("SELECT COUNT(*) todayNewMember FROM tb_member  WHERE CURDATE()=tb_member.create_date")
    Integer findTodayNewMember();

    //总会员
    @Select("SELECT COUNT(*) totalMember FROM tb_member ")
    Integer findTotalMember();

    //本周新增会员数
    @Select("SELECT COUNT(DISTINCT tb_member.id) thisWeekNewMember  FROM tb_member,rpt_date WHERE rpt_date.fweek=(SELECT fweek FROM rpt_date WHERE fdate=CURDATE()) AND fyear=(SELECT fyear FROM rpt_date WHERE fdate=CURDATE()) AND fdate=create_date")
    Integer findThisWeekNewMember();

    //本月新增会员数
    @Select("SELECT COUNT(*) thisMonthNewMember FROM tb_member,rpt_date WHERE rpt_date.fmonth=(SELECT fmonth FROM rpt_date WHERE fdate=CURDATE()) AND fyear=(SELECT fyear FROM rpt_date WHERE fdate=CURDATE()) AND fdate=create_date")
    Integer findThisMonthNewMember();

    //今日预约数
    @Select("SELECT COUNT(*) todayOrderNumber FROM tb_order WHERE order_date=CURDATE()")
    Integer findTodayOrderNumber();

    //今日到诊数
    @Select("SELECT COUNT(*) todayVisitsNumber FROM tb_order WHERE order_status=2 AND order_date=CURDATE()")
    Integer findTodayVisitsNumber();

    //本周预约数
    @Select("SELECT COUNT(*) thisWeekOrderNumber FROM rpt_date,tb_order  WHERE fweek=(SELECT fweek FROM rpt_date WHERE fdate=CURDATE()) AND fyear=(SELECT fyear FROM rpt_date WHERE fdate=CURDATE()) AND fdate=order_date")
    Integer findThisWeekOrderNumber();

    //本周到诊数
    @Select("SELECT COUNT(*) thisWeekVisitsNumber FROM tb_order,rpt_date WHERE order_status=2 AND fweek=(SELECT fweek FROM rpt_date WHERE fdate=CURDATE()) AND fyear=(SELECT fyear FROM rpt_date WHERE fdate=CURDATE()) AND fdate=order_date")
    Integer findThisWeekVisitsNumber();

    //本月预约数
    @Select("SELECT COUNT(*) thisWeekOrderNumber FROM rpt_date,tb_order  WHERE fmonth=(SELECT fmonth FROM rpt_date WHERE fdate=CURDATE()) AND fyear=(SELECT fyear FROM rpt_date WHERE fdate=CURDATE()) AND fdate=order_date")
    Integer findThisMonthOrderNumber();

    //本月到诊数
    @Select("SELECT COUNT(*) thisMonthVisitsNumber FROM tb_order,rpt_date WHERE order_status=2 AND fmonth=(SELECT fmonth FROM rpt_date WHERE fdate=CURDATE()) AND fyear=(SELECT fyear FROM rpt_date WHERE fdate=CURDATE()) AND fdate=order_date")
    Integer findThisMonthVisitsNumber();

    //热门套餐
    @Select("SELECT\n" +
            "  tb_setmeal.name,\n" +
            "  COUNT(setmeal_id)AS counts,\n" +
            "  CONCAT(LEFT(COUNT(setmeal_id)/conut_id*100,5),'%')    zhanbi\n" +
            "FROM tb_setmeal,\n" +
            "  tb_order,\n" +
            "  (SELECT\n" +
            "     COUNT(id) AS conut_id\n" +
            "   FROM tb_order) c\n" +
            "WHERE tb_setmeal.id = tb_order.setmeal_id\n" +
            "GROUP BY setmeal_id\n" +
            "ORDER BY counts DESC\n")
    List<HotSetmeal> findHostSetmeal();

}
